﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace DoeObjects
{
    public class Notification
    {
        public int NotificationId { get; set; }
        public User Recipient { get; set; }
        public User Sender { get; set; } //sender can be null in case of the notification was send by the system
        public bool Read { get; set; }
        public bool Done { get; set; }
        public string Message { get; set; }

        public DateTime SendDate { get; set; }

        public NotificationType Type { get; set; }

        private const string BASE_SQL = @"
SELECT * FROM [notification] 
INNER JOIN [User] 
ON [User].UserId = Recipient
INNER JOIN UserCategory
ON [User].CategoryId = UserCategory.UserCategoryId
";

        public static Notification getById(int id)
        {
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + "WHERE NotificationId = @id", connection);
            command.Parameters.AddWithValue("@id", id);

            Notification notification = null;
            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    notification = getCurrentNotification(reader);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return notification;
        }

        public static List<Notification> getAllForUser(int userId)
        {
            List<Notification> notifications = new List<Notification>();

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + "WHERE Recipient = @userId", connection);
            command.Parameters.AddWithValue("@userId", userId);

            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    notifications.Add(getCurrentNotification(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return notifications;
        }

        /// <summary>
        /// returns all notifications from a user.
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="unreadMessages">if set to true, it returns only the unread messages. otherwise only the read messages</param>
        /// <returns></returns>
        public static List<Notification> getAllForUser(int userId, bool unreadMessages)
        {
            List<Notification> notifications = new List<Notification>();

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand command = new SqlCommand(BASE_SQL + "WHERE Recipient = @userId AND [Read] = @read", connection);
            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@read", (unreadMessages) ? 0 : 1);

            try
            {
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    notifications.Add(getCurrentNotification(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return notifications;
        }

        public static int add(Notification notification)
        {
            int lastInsertedId = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("INSERT INTO [notification] (Recipient, Sender, [Read], Done, Message, NotificationType, SendDate) OUTPUT INSERTED.Notificationid VALUES (@Recipient, @Sender, @Read, @Done, @Message, @Type, GETDATE())", connection);

            SqlParameter msgParam = new SqlParameter("@Message", SqlDbType.VarChar);
            msgParam.Value = notification.Message;
            cmd.Parameters.Add(msgParam);
            cmd.Parameters.AddWithValue("@Recipient", notification.Recipient.UserId);
            cmd.Parameters.AddWithValue("@Sender", (notification.Sender == null)?DBNull.Value:(Object)notification.Sender.UserId);
            cmd.Parameters.AddWithValue("@Read", notification.Read);
            cmd.Parameters.AddWithValue("@Done", notification.Done);
            cmd.Parameters.AddWithValue("@Type", (notification.Type == NotificationType.UNKNOWN) ? DBNull.Value : (object)((int)notification.Type));

            try
            {
                connection.Open();
                Object res = cmd.ExecuteScalar();
                if (res != null)
                {
                    lastInsertedId = (int)res;
                }
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return lastInsertedId;
        }

        /// <summary>
        /// updates an existing publisher in the database
        /// </summary>
        /// <param name="notification"></param>
        /// <returns></returns>
        public static bool update(Notification notification)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("UPDATE [notification] SET Recipient = @Recipient, Sender = @Sender, [Read] = @Read, Done = @Done, Message = @Message, NotificationType = @Type, SendDate = @sendDate WHERE notificationId = @id", connection);

            SqlParameter msgParam = new SqlParameter("@Message", SqlDbType.VarChar);
            msgParam.Value = notification.Message;
            cmd.Parameters.Add(msgParam);
            cmd.Parameters.AddWithValue("@Recipient", notification.Recipient.UserId);
            cmd.Parameters.AddWithValue("@Sender", (notification.Sender == null) ? DBNull.Value : (Object)notification.Sender.UserId);
            cmd.Parameters.AddWithValue("@Read", notification.Read);
            cmd.Parameters.AddWithValue("@Done", notification.Done);
            cmd.Parameters.AddWithValue("@id", notification.NotificationId);
            cmd.Parameters.AddWithValue("@Type", (notification.Type == NotificationType.UNKNOWN) ? DBNull.Value : (object)((int)notification.Type));
            cmd.Parameters.AddWithValue("@sendDate", notification.SendDate);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

        public static bool delete(int notificationId)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("DELETE FROM [notification] WHERE notificationId = @id", connection);

            cmd.Parameters.AddWithValue("@id", notificationId);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

        public bool save()
        {
            if (this.NotificationId == 0)
            {
                this.NotificationId = Notification.add(this);
                return (this.NotificationId > 0);
            }
            else
            {
                return Notification.update(this);
            }
        }

        internal static Notification getCurrentNotification(SqlDataReader reader)
        {
            Notification notification = null;

            if (reader != null)
            {
                notification = new Notification();
                notification.NotificationId = (int)reader["NotificationId"];
                notification.Recipient = User.getCurrentUser(reader);
                notification.Sender = (reader["Sender"] == DBNull.Value) ? null : User.getUserByUserId((int)reader["Sender"]);
                notification.Read = (bool)reader["Read"];
                notification.Done = (bool)reader["Done"];
                notification.Message = reader["Message"] as string;
                notification.Type = (reader["NotificationType"] == DBNull.Value) ? NotificationType.UNKNOWN : (NotificationType)((int)reader["NotificationType"]);// getNotificationEnumValue((Object)reader["NotificationType"]);
                notification.SendDate = (DateTime)reader["SendDate"];
            }

            return notification;
        }

        //private static Object getNotificationIntValue(NotificationType type)
        //{
        //    switch (type)
        //    {
        //        case NotificationType.REGISTRATION_ACCEPT_REQUEST:
        //            return 0;
        //        case NotificationType.TITLE_RESERVATION:
        //            return 1;
        //        case NotificationType.EXPIRED_RECALL:
        //            return 2;

        //        case NotificationType.UNKNOWN: //give through to default:
        //        default:
        //            return DBNull.Value;
        //    }
        //}

        //private static NotificationType getNotificationEnumValue(Object value)
        //{
        //    if (value.Equals(0))
        //    {
        //        return NotificationType.REGISTRATION_ACCEPT_REQUEST;
        //    }
        //    else if (value.Equals(1))
        //    {
        //        return NotificationType.TITLE_RESERVATION;
        //    }
        //    else if (value.Equals(2))
        //    {
        //        return NotificationType.EXPIRED_RECALL;
        //    }
        //    else
        //    {
        //        return NotificationType.UNKNOWN;
        //    }
        //}

        /// <summary>
        /// marks a notification as read
        /// </summary>
        /// <param name="notificationId"></param>
        /// <returns></returns>
        public static bool markNotificationAsRead(int notificationId)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("UPDATE [notification] SET [Read] = '1' WHERE notificationId = @id", connection);

            cmd.Parameters.AddWithValue("@id", notificationId);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }
    }
}
